#-- coding: utf-8 --

'''
Author: 普拉斯777
Date: 2023年6月16日
Description: 汇总考勤、补卡、出差等多个表格
'''

import openpyxl
import os

def load_data(filename):
    wb = openpyxl.load_workbook(filename)
    ws = wb.active
    data = {}
    for row in ws.iter_rows(min_row=2, values_only=True):
        name = row[0]
        if name in data:
            data[name].append(row[1:])
        else:
            data[name] = [row[1:]]
    return data

def merge_data(yue_file, buka_file, chuchai_file, jiaban_file, qingjia_file,rank_file,output_file):
    # 加载数据
    yue_data = load_data(yue_file)
    buka_data = load_data(buka_file)
    chuchai_data = load_data(chuchai_file)
    jiaban_data = load_data(jiaban_file)
    qingjia_data = load_data(qingjia_file)
    rank_data = load_data(rank_file)

    # 合并数据
    merged_data = []
    for name, yue_row in yue_data.items():
        #print('yue_row',list(yue_row[0]))
        
        buka_row = buka_data.get(name, [[]])[0] if buka_data.get(name) is not None and buka_data.get(name, [[]])[0] is not None else ['']
        chuchai_row = chuchai_data.get(name, [[]])[0] if chuchai_data.get(name) is not None and chuchai_data.get(name, [[]])[0] is not None else ['']
        jiaban_row = jiaban_data.get(name, [[]])[0] if jiaban_data.get(name) is not None and jiaban_data.get(name, [[]])[0] is not None else ['']
        qingjia_row = qingjia_data.get(name, [[]])[0] if qingjia_data.get(name) is not None and qingjia_data.get(name, [[]])[0] is not None else ['']
        
        rank_row = rank_data.get(name, [[]])[0]

        

        #print(buka_row,chuchai_row,jiaban_row,qingjia_row,rank_row)
        
        merged_row = list(rank_row) + [name] + list(yue_row[0]) + list(chuchai_row) + list(qingjia_row) + list(jiaban_row) + list(buka_row) 
        #print(list(jiaban_row))
        
        merged_data.append(merged_row)


    # 写入新的 Excel 文件
    output_dir = "./"
    os.makedirs(output_dir, exist_ok=True)  # 如果目录不存在，就创建目录
    output_path = os.path.join(output_dir, output_file)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "X月考勤汇总"
    ws.append(["内部排序","姓名",  "应出勤天数", "出勤天数（工作日）", "出差天数合计", "请假天数合计", "加班时长合计", "补卡次数"])
    for row in merged_data:
        ws.append(row)
        #print(row)
    #exit()
    wb.save(output_path)

    print(f"处理完成，结果已保存到文件：{output_path}")

def main():
    yue_file = "yue-cute.xlsx"
    buka_file = "buka-count.xlsx"
    chuchai_file = "chuchai-sum.xlsx"
    jiaban_file = "jiaban-sum.xlsx"
    qingjia_file = "qingjia-sum.xlsx"
    rank_file = "_rank.xlsx"
    
    output_file = "X月考勤汇总2.xlsx"

    merge_data(yue_file, buka_file, chuchai_file, jiaban_file, qingjia_file, rank_file, output_file)

if __name__ == "__main__":
    main()
